package com.meet.dao;

import com.meet.entity.BoardRoom;
import com.meet.entity.Department;
import com.meet.util.JDBCutil;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

/**
 * @author RicardoZeng
 * @date 2022/8/11 19:44
 */
public class DepartmentDaoImpl implements DepartmentDao{
    private PreparedStatement preparedStatement;
    private ResultSet resultSet;

    @Override
    public List<Department> getAllDepartment(int currentPage,int rows) throws SQLException {
        List<Department> lists = new ArrayList<Department>();

        //连接数据库
        Connection conn = JDBCutil.getConnection();

        String sql = "select * from department limit ?,?";
        preparedStatement = conn.prepareStatement(sql);
        preparedStatement.setObject(1,currentPage);
        preparedStatement.setObject(2,rows);

        resultSet = preparedStatement.executeQuery();

        while(resultSet.next()){
            Department department = new Department();

            department.setId(resultSet.getInt(1));
            department.setDptName(resultSet.getString(2));

            lists.add(department);
        }

        JDBCutil.closeConnection();
        return lists;
    }

    @Override
    public int addDepartment(Department department) throws SQLException {
        //连接数据库
        Connection conn = JDBCutil.getConnection();

        String sql = "insert into department(d_name) values (?)";
        preparedStatement = conn.prepareStatement(sql);
        preparedStatement.setObject(1,department.getDptName());
        int count = preparedStatement.executeUpdate();

        JDBCutil.closeConnection();
        return count;
    }

    @Override
    public int delDepartment(Integer Id) throws SQLException {
        //连接数据库
        Connection conn = JDBCutil.getConnection();

        String sql = "delete  from department where id = ?";
        preparedStatement = conn.prepareStatement(sql);
        preparedStatement.setObject(1,Id);

        int count = preparedStatement.executeUpdate();


        JDBCutil.closeConnection();
        return count;
    }

    @Override
    public int updateDepartment(Department department) throws SQLException {
        //连接数据库
        Connection conn = JDBCutil.getConnection();

        String sql = "update department set d_name=? where id=?";
        preparedStatement = conn.prepareStatement(sql);
        preparedStatement.setObject(1,department.getDptName());
        preparedStatement.setObject(2,department.getId());

        int count = preparedStatement.executeUpdate();

        JDBCutil.closeConnection();
        return count;
    }

    @Override
    public Department getDptId(Integer Id) throws SQLException {

        Department department = new Department();
        //连接数据库
        Connection conn = JDBCutil.getConnection();

        String sql = "select * from department where id = ?";
        preparedStatement = conn.prepareStatement(sql);
        preparedStatement.setObject(1,Id);

        resultSet = preparedStatement.executeQuery();

        if(resultSet.next()){

            department.setId(resultSet.getInt(1));
            department.setDptName(resultSet.getString(2));


        }

        JDBCutil.closeConnection();
        return department;
    }

    @Override
    public int count() throws SQLException {
        //连接数据库
        Connection conn = JDBCutil.getConnection();

        String sql = "select count(1) from department";
        preparedStatement = conn.prepareStatement(sql);

        resultSet = preparedStatement.executeQuery();
        int count = 0;
        if (resultSet.next()) {
            count = resultSet.getInt(1);
        }
        JDBCutil.closeConnection();
        return count;
    }

    @Override
    public List<Department> getDptName() throws SQLException {
        Department department = new Department();
        //连接数据库
        Connection conn = JDBCutil.getConnection();

        String sql = "select * from department ";
        preparedStatement = conn.prepareStatement(sql);
        resultSet = preparedStatement.executeQuery();
        List<Department> lists=new ArrayList<Department>();
        if(resultSet.next()){
            department.setId(resultSet.getInt(1));
            department.setDptName(resultSet.getString(2));
        }
        JDBCutil.closeConnection();
        return lists;
    }
}
